package com.cfs.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;
 
/**
 * 自定义表单
 * @author zz
 *
 */
@Service
public class CfsServiceImpl {
	
	
	@Autowired
    private JdbcTemplate jdbcTemplate;

	public boolean isExistFiled(String table_name,String filed) 
	{
		boolean ret = false;
		String sql = "select * from " + table_name;
		SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet(sql);  
		SqlRowSetMetaData sqlRsmd = sqlRowSet.getMetaData();  
		int columnCount = sqlRsmd.getColumnCount();  
		for (int i = 1; i <= columnCount; i++) 
		{  
			// 获得指定列的列名
			String columnName = sqlRsmd.getColumnName(i);
			if(filed.equals(columnName)){
				System.out.println("存在字段="+columnName);
				ret = true ; 
				break; 
			}else{
				System.out.println("不匹配字段="+columnName);
				ret = false ;
			}		
		}
		return ret;
	}
	public boolean  tableNameExist(String tableName) throws  Exception {  
		Connection conn = null;
		boolean flag = false;
		try {
			conn = jdbcTemplate.getDataSource().getConnection();
			ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null);
			if (rs.next()) {  
				flag = true;  
	        }else {  
	        	flag = false;  
	        }  
		}catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	conn.close();
	    }
//		DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
//		conn.close();
        return flag;
	} 
	/**
	 * 插入表记录
	 * @param title
	 * @param name
	 * @param type
	 * @param str_json
	 * @return
	 * @throws Exception
	 */
	public int  insertAssistantTable(String title,String name,String type,String str_json) throws Exception {
		
		    int i=0; 
		    String sql="insert into table_manage(title,name,type,str_json) values('"+title+"','"+name+"','"+type+"','"+str_json+"')"; 
		    System.out.println(sql);
		    Connection conn = null;
		    PreparedStatement  preStmt = null;
		    try {
		    	conn = jdbcTemplate.getDataSource().getConnection();
			    preStmt =conn.prepareStatement(sql);   
			    i=preStmt.executeUpdate();  
			    System.out.println("创建副表成功："+i);
		    }catch(Exception e) {
		    	e.getMessage();
		    }finally {
		    	preStmt.close();
		    	conn.close();
		    }
//		    DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
//		    conn.close();
		    return i;//返回影响的行数，1为执行成功  
	}
	
	/**
	 * 更新表记录
	 * @param id
	 * @param title
	 * @param name
	 * @param type
	 * @param str_json
	 * @return
	 * @throws Exception
	 */
	public int  updatAssistantTable(String title,String name,String type,String str_json) throws Exception {
		
	    int i=0; 
	    String sql="update table_manage set title= '"+title+"',name='"+name+"',type='"+type+"',str_json='"+str_json+"' where title='"+title+"'"; 
	    System.out.println(sql);
	    Connection conn = null;
	    PreparedStatement  preStmt = null;
	    try {
	    	conn = jdbcTemplate.getDataSource().getConnection();
		    preStmt =conn.prepareStatement(sql);   
		    i=preStmt.executeUpdate();  
		    System.out.println("创建副表成功："+i);
	    }catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	preStmt.close();
	    	conn.close();
	    }
//	    PreparedStatement  preStmt =jdbcTemplate.getDataSource().getConnection().prepareStatement(sql);  
//	        i=preStmt.executeUpdate();  
//	        System.out.println("创建副表成功："+i);
//	        preStmt.close();
	    return i;//返回影响的行数，1为执行成功  
	}
	
	
    public String getJsonByTableName(String tableName) throws Exception {
	    String json = ""; 
        String sql = "select * from table_manage where title='"+tableName+"'";  
        Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcTemplate.getDataSource().getConnection();
			stmt = conn.createStatement();  
			ResultSet rs = stmt.executeQuery(sql);  
			if(rs.next()){   
	        	 json = rs.getString(5);  
	        }  
		}catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	stmt.close();
	    	conn.close();
	    }
//        Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
//        ResultSet rs = stmt.executeQuery(sql);  
//        if(rs.next()){   
//        	 json = rs.getString(5);  
//        }  
//        //可以将查找到的值写入类，然后返回相应的对象  
//        stmt.close();
        return json;	
    }
    public List<Map<String, Object>> getByType(String type) throws Exception {
    	
        String sql = "select * from table_manage where type='"+type+"'";  
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        System.out.println("list="+list);
        /*Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        ResultSet rs = stmt.executeQuery(sql);  
        if(rs.next()){   
        	 json = rs.getString(5);  
        }  */
        //可以将查找到的值写入类，然后返回相应的对象  
        return list;	
    }
    
    
    /**
     * 创建数据库表
     * @param table_name
     * @return
     */
	public boolean createTable(String table_name)
	{
		String sql = "create table "+table_name+"(id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,uuid VARCHAR(255),create_user_id int(11),taskid VARCHAR(255))";
		try
		{
			jdbcTemplate.execute(sql);
			return true;
		}
		catch(org.springframework.dao.DataAccessException e)
		{
			return false;
		}
	}
	/**
	 * 根据表名，添加对应点列
	 * @param table_name
	 * @param colume_name
	 * @param type
	 * @return
	 */
	public boolean addColume(String table_name,String colume_name,String type)
	{
		String sql = "alter table "+table_name+" add "+colume_name+" "+type;
		try
		{
			jdbcTemplate.execute(sql);
			return true;
		}
		catch(org.springframework.dao.DataAccessException e)
		{
			return false;
		}
	}
	/**
	 * 根据表名，删除对应点列
	 * @param table_name
	 * @param colume_name
	 * @param type
	 * @return
	 */
	public boolean deleteColume(String table_name,String colume_name)
	{
		String sql = "alter table "+table_name+" DROP column "+colume_name;
		try
		{
			jdbcTemplate.execute(sql);
			return true;
		}
		catch(org.springframework.dao.DataAccessException e)
		{
			return false;
		}
	}
	
	 /**
	  * 拿到json 解析，创建数据库和表
	  * @param jsonStr
	  * @return boolean
	  * @throws Exception
	  */
	public boolean doMethod(String jsonStr) throws Exception{
		System.out.println("jsonStr="+jsonStr);
		boolean ret =false ;
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title");
		String name = obj.getString("name");
		String ttt = obj.getString("type"); 
		
		JSONArray arrayFiled  = obj.getJSONArray("field");
		
		if(tableNameExist(tableName)){
			System.out.println("表名存在,添加数据列");
			//存在  更新数据
			updatAssistantTable(tableName, name, ttt, jsonStr);
			for(int i=0;i<arrayFiled.length();i++){
				JSONObject  o =arrayFiled.getJSONObject(i);
				String columeName = o.getString("text");
				String type= o.getString("type");
				if(isExistFiled(tableName, columeName)){
					ret = true;
					continue ;
				}else{
					ret = addColume(tableName, columeName, type);
				}
		 		 
			}
		}else{
			System.out.println("表名不存在，创建数据表,添加数据列");
			ret =  createTable(tableName);
			//不存在  新建数据
			insertAssistantTable(tableName, name, ttt, jsonStr);
			for(int i=0;i<arrayFiled.length();i++){
				JSONObject  o =arrayFiled.getJSONObject(i);
				String columeName = o.getString("text");
				String type = o.getString("type");
				if(isExistFiled(tableName, columeName)){
					ret = true;
					System.out.println("字段存在="+ret);
					continue ;
				}else{ 	
					ret = addColume(tableName, columeName, type);
					System.out.println("字段bu存在="+ret);

				} 
			}
		}
		return ret; 
	}
	
	
	public int  insertSelectTable(String tableName,String title,String type,String textField,String valueField) throws Exception {
		
	    int i=0; 
	    String sql="insert into table_manage_select(table_name,title,type,text_field,value_field) "
	    		+ "values('"+tableName+"','"+title+"','"+type+"','"+textField+"','"+valueField+"')"; 
	    
	    System.out.println(sql);
	    Connection conn = null;
	    PreparedStatement  preStmt = null;
	    try {
	    	conn = jdbcTemplate.getDataSource().getConnection();
	    	preStmt =conn.prepareStatement(sql);
		    i=preStmt.executeUpdate();  
	    }catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	preStmt.close();
	    	conn.close();
	    }
	    //preStmt.close();
	    System.out.println("创建副表成功："+i);
	    return i;//返回影响的行数，1为执行成功  
	}
	
	/**
	 * 更新表记录
	 * @param id
	 * @param title
	 * @param name
	 * @param type
	 * @param str_json
	 * @return
	 * @throws Exception
	 */
	public int  updateSelectTable(String tableName,String title,String type,String textField,String valueField) throws Exception {
			
			    int i=0; 
			    String sql="update table_manage_select set "
			    		+ "table_name= '"+tableName+"',"
			    		+ "title= '"+title+"',"
			    		+ "type='"+type+"',"
			    		+ "text_field='"+textField+"',"
						+ "value_field='"+valueField+"'"
						+ " where table_name='"+tableName+"'"; 
			    System.out.println(sql);
			    Connection conn = null;
			    PreparedStatement  preStmt = null;
			    try {
			    	 conn = jdbcTemplate.getDataSource().getConnection();
			 	     preStmt =conn.prepareStatement(sql);
			         i=preStmt.executeUpdate();  
			    }catch(Exception e) {
			    	e.getMessage();
			    }finally {
			    	preStmt.close();
			    	conn.close();
			    }
				System.out.println("创建副表成功："+i);
			    return i;//返回影响的行数，1为执行成功  
	}
	/**
	 * 更新表记录
	 * @param id
	 * @param title
	 * @param name
	 * @param type
	 * @param str_json
	 * @return
	 * @throws Exception
	 */
	public int  deleteSelectTable(String tableName,String type) throws Exception {
				if("0".equals(type)){
					deleteSelectTableByType(tableName, type);
			    }
			    int i=0; 
			    String sql = "delete from table_manage_select where table_name='"+tableName+"'";
			    System.out.println(sql);
			    Connection conn = null;
			    PreparedStatement  preStmt = null;
			    try {
			    	 conn = jdbcTemplate.getDataSource().getConnection();
			 	     preStmt =conn.prepareStatement(sql);
			         i=preStmt.executeUpdate();  
			    }catch(Exception e) {
			    	e.getMessage();
			    }finally {
			    	preStmt.close();
			    	conn.close();
			    }
//			    PreparedStatement  preStmt =jdbcTemplate.getDataSource().getConnection().prepareStatement(sql);
//		        i=preStmt.executeUpdate();  
		        System.out.println("创建副表成功："+i);
//		        preStmt.close();
			    return i;//返回影响的行数，1为执行成功 	   
	}
	public int  deleteSelectTableByType(String tableName,String type) throws Exception {
		
	    int i=0; 
	    String sql = "drop table " + tableName;
	    System.out.println(sql);
	    Connection conn = null;
	    PreparedStatement  preStmt = null;
	    try {
	    	 conn = jdbcTemplate.getDataSource().getConnection();
	 	     preStmt =conn.prepareStatement(sql);
	         i=preStmt.executeUpdate();  
	    }catch(Exception e) {
	    	e.getMessage();
	    }finally {
	    	preStmt.close();
	    	conn.close();
	    }
//	    PreparedStatement  preStmt =jdbcTemplate.getDataSource().getConnection().prepareStatement(sql);
//        i=preStmt.executeUpdate();  
        System.out.println("创建副表成功："+i);
//        preStmt.close();
	    return i;//返回影响的行数，1为执行成功  
	}
	 
	/**
	 * 根据表名判断在table_manage_select表中 是否存在这个表名
	 * @param table_name
	 * @return
	 * @throws SQLException 
	 */
	public boolean isExistTableName(String table_name) throws SQLException {
		String sql = "select table_name from table_manage_select ";
	    Connection conn =null;
		PreparedStatement stmt =null;
		boolean ret= false ;
		try {
			conn = jdbcTemplate.getDataSource().getConnection();
			stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery(sql);
			System.out.println("sql = "+sql);
			ResultSetMetaData data = rs.getMetaData();
		    //ResultSetMetaData data = jdbcTemplate.getDataSource().getConnection().prepareStatement(sql).executeQuery(sql).getMetaData();
		    while(rs.next()) {
		    	for(int i=1;i<=data.getColumnCount();i++) {
		    		System.out.println(rs.getString(i)); 
		    		if(table_name.equals(rs.getString(i))){
		    			ret = true ;
		    		}
		    	}
		    }
//		    DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
//		    conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			stmt.close();
	    	conn.close();
	    }
		
		return ret;
	}
	
	/**
	 * 创建数据库下拉表
	 * @param table_name
	 * @return
	 */
	 public boolean createTableSelect(String table_name,String textField) {
			
		 String sql = "create table "+table_name+"(id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,"+textField+" varchar(255)) ";
		 System.out.println(sql);
		try
		{
			jdbcTemplate.execute(sql);
			return true;
		}
		catch(org.springframework.dao.DataAccessException e)
		{
			return false;
		}
	 }
	 
	 
		/**
		 * 拿到下拉选择框json 解析，创建数据和表
		 * @param jsonStr
		 * @return boolean
		 * @throws Exception
		 * 
		 * {"type":"1","tableName":"数据库表名","title":"数据表描述文件","textField":"显示字段name","valueField":"显示value_id"}
		 */
		public int doSelect(String jsonStr) throws Exception{
			System.out.println("jsonStr="+jsonStr);
			int i=-1;
			//拿到json 判断是否为空
			if(jsonStr!=null&&jsonStr!=""){
				
				JSONObject obj = new JSONObject(jsonStr);
				String type = obj.getString("type"); 
				String tableName = obj.getString("tableName");
				String title = obj.getString("title");
				String textField = obj.getString("textField");
				String valueField = obj.getString("valueField");
				if(isExistTableName(tableName)){
					//存在这个表名
					System.out.println("存在这个表=="+tableName);
					//-2  表名已存在
					i=-2;
				}else{
					//不存在这个表名
					System.out.println("不不不存在这个表=="+tableName);
					//0是本地数据  ，1是从其他表中获取的数据 ，
					if("0".equals(type)){
						createTableSelect(tableName,textField);
						System.out.println("0=="+type);
					} 
					i=insertSelectTable(tableName, title, type, textField, valueField);
				}
				return i;
			}
			return i;
		}
		public class XiaLa{
			int id;
			String text;
			public int getId() {
				return id;
			}
			public void setId(int id) {
				this.id = id;
			}
			public String getText() {
				return text;
			}
			public void setText(String text) {
				this.text = text;
			}
			
		}
	   public ArrayList<XiaLa> getTableName() throws Exception {
		    ArrayList<XiaLa> arr = new ArrayList<>();
	        String sql = "select  id, title  from  table_manage ";
	        Connection conn = null;
			 Statement stmt = null;
		    try {
			     conn = jdbcTemplate.getDataSource().getConnection();
				 stmt = conn.createStatement();
				 ResultSet rs = stmt.executeQuery(sql);
				 ResultSetMetaData data = rs.getMetaData();
				 while(rs.next()) {
				    		XiaLa x = new XiaLa();
				    		System.out.println("title="+rs.getString("id")); 
				    		System.out.println("id="+rs.getString("title"));
				    		x.setId(rs.getInt("id")); 
				    		x.setText(rs.getString("title"));
				    		arr.add(x);
				 }
				 //DataSourceUtils.releaseConnection(conn,jdbcTemplate.getDataSource());
				 conn.close();
	        } catch (SQLException e) {
				e.printStackTrace();
	        }finally {
	        	stmt.close();
		    	conn.close();
		    }
		    return arr ;
	   }
}
